package com.sh.data.engine.domain.shims.sql.select;

import com.sh.data.engine.domain.base.model.enums.Database;
import com.sh.data.engine.domain.shims.sql.Context;
import com.sh.data.engine.domain.shims.sql.IllegalQueryException;
import com.sh.data.engine.domain.shims.sql.TerminalExpression;
import org.apache.commons.lang3.RandomStringUtils;

import java.sql.SQLException;
import java.util.List;

public class Limit implements TerminalExpression {

    private final Context context;

    public Limit(Context context, int start, int size) {
        this.context = limit(context, start, size);
    }

    @Override
    public <E> List<E> list(RowMapper<E> rowMapper) throws SQLException {
        return context.list(rowMapper);
    }

    @Override
    public <E> E single(RowMapper<E> rowMapper) throws SQLException {
        return context.single(rowMapper);
    }

    private Context limit(Context context, int start, int size) {
        return new LimiterFactory().create(context.getDatabase()).limit(context, start, size);
    }

    @Override
    public String toString() {
        return context.toString();
    }
}

interface Limiter {
    Context limit(Context context, int start, int size);
}

class MySqlLimiter implements Limiter {

    @Override
    public Context limit(Context context, int start, int size) {
        context.appendWithSeparator("LIMIT");
        context.appendWithSeparator(start);
        context.appendWithSeparator(",");
        context.appendWithSeparator(size);
        return context;
    }
}

class OracleLimiter implements Limiter {

    @Override
    public Context limit(Context context, int start, int size) {
        String data = RandomStringUtils.randomAlphabetic(6);
        String ord_data = RandomStringUtils.randomAlphabetic(6);
        String rnum = RandomStringUtils.randomAlphabetic(6);

        Context c = new Context(context);
        c.appendWithSeparator("SELECT");
        c.appendWithSeparator(data + ".*");
        c.appendWithSeparator("FROM");
        c.appendWithSeparator("(");
        c.appendWithSeparator("SELECT");
        c.appendWithSeparator(ord_data + ".*,");
        c.appendWithSeparator("rownum AS " + rnum);
        c.appendWithSeparator("FROM");
        c.appendWithSeparator("(");
        c.appendWithSeparator(context.toString());
        c.appendWithSeparator(")");
        c.appendWithSeparator(ord_data);
        c.appendWithSeparator(")");
        c.appendWithSeparator(data);
        c.appendWithSeparator("WHERE");
        c.appendWithSeparator(rnum + " BETWEEN ? AND ?");
        c.addParameters(start);
        c.addParameters(start + size);
        return c;
    }
}

class PostgresSqlLimiter implements Limiter {

    @Override
    public Context limit(Context context, int start, int size) {
        context.appendWithSeparator("LIMIT ?");
        context.addParameters(size);
        context.appendWithSeparator("OFFSET ?");
        context.addParameters(start);
        return context;
    }
}

class SqlServerLimiter implements Limiter {

    @Override
    public Context limit(Context context, int start, int size) {
        if (!context.toString().toUpperCase().contains("ORDER")) {
            throw new IllegalQueryException("使用分页必须要有order by");
        }
        context.appendWithSeparator("OFFSET ?");
        context.addParameters(start);
        context.appendWithSeparator("ROWS FETCH NEXT ? ROWS ONLY");
        context.addParameters(size);
        return context;
    }
}

class DB2Limiter implements Limiter {

    @Override
    public Context limit(Context context, int start, int size) {
        String A = RandomStringUtils.randomAlphabetic(6);
        String B = RandomStringUtils.randomAlphabetic(6);
        String TN = RandomStringUtils.randomAlphabetic(6);

        Context c = new Context(context);
        c.appendWithSeparator("SELECT");
        c.appendWithSeparator("*");
        c.appendWithSeparator("FROM");
        c.appendWithSeparator("(");
        c.appendWithSeparator("SELECT");
        c.appendWithSeparator(B + ".*,");
        c.appendWithSeparator("ROWNUMBER()");
        c.appendWithSeparator("OVER() AS " + TN);
        c.appendWithSeparator("FROM");
        c.appendWithSeparator("(");
        c.appendWithSeparator(context.toString());
        c.appendWithSeparator(") AS " + B);
        c.appendWithSeparator(") AS " + A);
        c.appendWithSeparator("WHERE");
        c.appendWithSeparator(A + "." + TN + " BETWEEN ? AND ?");
        c.addParameters(start);
        c.addParameters(start + size - (start == 0 ? 0 : 1));
        return c;
    }
}

class DefaultLimiter implements Limiter {
    @Override
    public Context limit(Context context, int start, int size) {
        return context;
    }
}

class LimiterFactory {
    Limiter create(Database database) {
        switch (database) {
            case MYSQL:
            case ESSQL:
            case TIDB:
            case CLICKHOUSE:
                return new MySqlLimiter();
            case ORACLE:
                return new OracleLimiter();
            case POSTGRESQL:
            case SAP:
            case IoTDB:
            case KINGBASE8:
                return new PostgresSqlLimiter();
            case SQLSERVER:
                return new SqlServerLimiter();
            default:
                return new DefaultLimiter();
        }
    }
}
